﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DAL;

namespace KPIS.GERP.GBMS.DAL
{
    [Serializable]
    public sealed class SourceTypeDAL : ISourceTypeDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public string DuplicateCheck(SourceTypeInfo srcTypeInfo)
        {
            try
            {
                dbManager.Open();
                IDataReader rdr = null;
                if (srcTypeInfo.Sequence != 0)
                {
                    dbManager.CreateParameters(3);
                    dbManager.AddParameters(0, "@code", srcTypeInfo.Code);
                    dbManager.AddParameters(1, "@status", SystemInfo._ActiveRecordStatus);
                    dbManager.AddParameters(2, "@currentseq", srcTypeInfo.Sequence);

                    string strSQL = @"SELECT * FROM BGM_MAS_SOURCE_TYPE
                                WHERE record_status = @status AND
                                bgm_source_type_code = @code  AND 
                                bgm_source_type_seq != @currentseq";

                    rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);
                }
                else
                {
                    dbManager.CreateParameters(2);
                    dbManager.AddParameters(0, "@code", srcTypeInfo.Code);
                    dbManager.AddParameters(1, "@status", SystemInfo._ActiveRecordStatus);

                    string strSQL = @"SELECT * FROM BGM_MAS_SOURCE_TYPE
                                WHERE record_status = @status AND
                                bgm_source_type_code = @code";

                    rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);
                }

                while (rdr != null && rdr.Read())
                {
                    SourceTypeInfo scrRecord = new SourceTypeInfo(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.IsDBNull(3) ? "" : rdr.GetString(3), rdr.GetString(4), null);
                    dbManager.CloseReader();
					return String.Format("รหัส \"{1}\" ในขณะนี้ ถูกใช้โดย <b>\"{0}\"</b> กรุณาเลือกรหัสที่ไม่ซ้ำ", scrRecord.Name, srcTypeInfo.Code);
                }
                return "";
            }
            catch (Exception err)
            {
                return err.Message;
            }
            finally
            {
                if(!dbManager.DataReader.IsClosed)
                    dbManager.CloseReader();
                dbManager.Dispose();
            }
        }


        private int GetNewCodeIdNotDuplicate()
        {
            try
            {
                //dbManager.Open();
                IDataReader rdr = null;

                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@status", SystemInfo._ActiveRecordStatus);
                string strSQL = @"SELECT bgm_source_type_code
                                  FROM BGM_MAS_SOURCE_TYPE 
                                  WHERE record_status = @status
                                  ORDER BY bgm_source_type_code";

                rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);
                
                var availableCode = new List<int>();
                while (rdr != null && rdr.Read())
                {
                    availableCode.Add(rdr.GetInt32(0));
                }

                int newCodeId = 1;
                while(availableCode.Contains(newCodeId))
                {
                    newCodeId++;
                }
                return newCodeId;
            }
            catch (Exception err)
            {
                return -1;
            }
            finally
            {
                //dbManager.CloseReader();
                //dbManager.Dispose();
            }
        }

        public int Insert(SourceTypeInfo srcTypeInfo)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@code", srcTypeInfo.Code);
                dbManager.AddParameters(1, "@name", srcTypeInfo.Name);
                dbManager.AddParameters(2, "@descr", srcTypeInfo.Description);
                dbManager.AddParameters(3, "@status", SystemInfo._ActiveRecordStatus);

                return dbManager.ExecuteNonQuery(CommandType.Text, "INSERT INTO BGM_MAS_SOURCE_TYPE(BGM_SOURCE_TYPE_CODE, BGM_SOURCE_TYPE_NAME, BGM_SOURCE_TYPE_DESCR, RECORD_STATUS) VALUES (@code, @name, @descr, @status)");
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public int Update(SourceTypeInfo srcTypeInfo)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5); 
                dbManager.AddParameters(0, "@seq", srcTypeInfo.Sequence);
                dbManager.AddParameters(1, "@code",  srcTypeInfo.Code);
                dbManager.AddParameters(2, "@name", srcTypeInfo.Name);
                dbManager.AddParameters(3, "@descr", srcTypeInfo.Description);
                dbManager.AddParameters(4, "@status", SystemInfo._ActiveRecordStatus);

                strSQL = "UPDATE BGM_MAS_SOURCE_TYPE"
                + " SET BGM_SOURCE_TYPE_CODE = @code"
                    + ", BGM_SOURCE_TYPE_NAME = @name"
                    + ", BGM_SOURCE_TYPE_DESCR = @descr"
                    + ", RECORD_STATUS = @status"
                + " WHERE BGM_SOURCE_TYPE_SEQ = @seq";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public int Delete(int seq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                return dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE BGM_MAS_SOURCE_TYPE SET RECORD_STATUS = @status WHERE BGM_SOURCE_TYPE_SEQ = @seq");
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public IList<SourceTypeInfo> Search(SourceTypeInfo srcTypeInfo)
        {
            List<SourceTypeInfo> srcType = new List<SourceTypeInfo>();
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@seq", srcTypeInfo.Sequence);
                dbManager.AddParameters(1, "@code", srcTypeInfo.Code);
                dbManager.AddParameters(2, "@name", srcTypeInfo.Name);
                dbManager.AddParameters(3, "@descr", srcTypeInfo.Description);
                dbManager.AddParameters(4, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT BGM_SOURCE_TYPE_SEQ, BGM_SOURCE_TYPE_CODE, BGM_SOURCE_TYPE_NAME, BGM_SOURCE_TYPE_DESCR, RECORD_STATUS"
                    + " FROM BGM_MAS_SOURCE_TYPE"
                    + " WHERE RECORD_STATUS not like @status"
                        + ((srcTypeInfo.Sequence > 0) ? " AND BGM_SOURCE_TYPE_SEQ = @seq" : "")
                        + ((srcTypeInfo.Code.Length > 0) ? " AND BGM_SOURCE_TYPE_CODE = @code" : "")
                        + ((srcTypeInfo.Name.Length > 0) ? " AND BGM_SOURCE_TYPE_NAME = @name" : "")
                        + ((srcTypeInfo.Description.Length > 0) ? " AND BGM_SOURCE_TYPE_DESCR = @descr" : "");


                //strSQL = "SELECT emp_seq AS BGM_SOURCE_TYPE_SEQ, emp_seq as BGM_SOURCE_TYPE_CODE, CONCAT(p.prefix_short, first_name, ' ', last_name) AS BGM_SOURCE_TYPE_NAME, first_name as BGM_SOURCE_TYPE_DESCR"
                //    + ", e.RECORD_STATUS"
                //    + " FROM gerp.BGM_MAS_SOURCE_TYPE s"
                //        + " inner join ghrms.HRM_PS_EMPLOYEES e on s.created_by = e.emp_seq"
                //        + " inner join ghrms.mas_name_prefixes p on e.name_prefix_seq = p.name_prefix_seq";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    SourceTypeInfo scrRecord = new SourceTypeInfo(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.IsDBNull(3) ? "" : rdr.GetString(3), rdr.GetString(4), null);
                    srcType.Add(scrRecord);
                }

                return srcType;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public IList<TreeViewInfo> TreeView(IList<TreeViewInfo> treeView)
        {
            string strSQL = "SELECT CONCAT('SOURCETYPE-', T.BGM_SOURCE_TYPE_SEQ) AS ID, T.BGM_SOURCE_TYPE_NAME AS NAME, NULL AS PARENT"
                + ", T.BGM_SOURCE_TYPE_NAME AS TEXT_FIELD, CONCAT('SOURCETYPE-', T.BGM_SOURCE_TYPE_SEQ) AS VALUE_FIELD"
                + " FROM BGM_MAS_SOURCE_TYPE T"
                + " WHERE T.RECORD_STATUS not like @status";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    TreeViewInfo sourceType = new TreeViewInfo(rdr.GetString(0), rdr.GetString(1), (rdr.IsDBNull(2)) ? null : rdr.GetString(2), rdr.GetString(3), rdr.GetString(4));
                    treeView.Add(sourceType);
                }

                return treeView;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
    }
}

/*

SELECT CONCAT('A-', T.BGM_SOURCE_TYPE_SEQ) AS ID, T.BGM_SOURCE_TYPE_NAME AS NAME, NULL AS PARENT
FROM BGM_MAS_SOURCE_TYPE T
WHERE T.RECORD_STATUS not like 'D'

UNION ALL

SELECT CONCAT('B-', S.BGM_SOURCE_SEQ) AS ID, S.BGM_SOURCE_NAME AS NAME, COALESCE(CONCAT('B-', UPPER_BGM_SOURCE_SEQ), CONCAT('A-', S.BGM_SOURCE_TYPE_SEQ)) AS PARENT
FROM BGM_MAS_SOURCE S
WHERE S.RECORD_STATUS not like 'D'

UNION ALL

SELECT CONCAT('C-', EG.BGM_EXP_GRP_SEQ) AS ID, EG.BGM_EXP_GRP_NAME AS NAME, COALESCE(CONCAT('C-', EG.UPPER_BGM_EXP_GRP_SEQ), CONCAT('B-', EG.BGM_SOURCE_SEQ)) AS PARENT
FROM BGM_MAS_EXPENSE_GROUP EG
WHERE RECORD_STATUS not like 'D'

UNION ALL

SELECT CONCAT('D-', E.BGM_EXP_SEQ) AS ID, E.BGM_EXP_NAME AS NAME, COALESCE(CONCAT('D-', E.UPPER_BGM_EXP_SEQ), CONCAT('C-', E.BGM_EXP_GRP_SEQ)) AS PARENT
FROM BGM_MAS_EXPENSE E
WHERE E.RECORD_STATUS not like 'D'


*/